﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Musicman.Common.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using Musicman.Common;

namespace Musicman.DAL
{
    public class UserMessageDAO
    {
        # region singleton definition
        static readonly UserMessageDAO instance = new UserMessageDAO();

        public static UserMessageDAO Instance
        {
            get
            {
                return instance;
            }
        }
        static UserMessageDAO()
        {
        }
        # endregion

        #region select 

        public List<UserMessage> LoadUserMessages(string where, string orderby, IEnumerable<SelectParameter> parameters) 
        {
            List<UserMessage> res = new List<UserMessage>();
            string selectSql = String.Format(@"
SELECT UserPost.ID,UserPost.Subject,UserPost.Message,UserPost.Date,  
	   Users.Personid, Users.FirstName, Users.LastName, Users.EmailAddress,
	   Comments.ID,Comments.Subject,Comments.Message,Comments.Date,
	   CommentUsers.Personid, CommentUsers.FirstName, CommentUsers.LastName, CommentUsers.EmailAddress
FROM UserPost
INNER JOIN Users ON UserPost.Personid = Users.PersonID
LEFT OUTER JOIN UserPost Comments on UserPost.ID = Comments.ParentID
LEFT OUTER JOIN Users CommentUsers ON Comments.Personid = CommentUsers.PersonID
WHERE UserPost.ParentID is null {0} {1}", where, orderby);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = db.GetSqlStringCommand(selectSql);
                foreach (SelectParameter parameter in parameters)
                {
                    db.AddInParameter(cmd, parameter.ParameterName, parameter.DbType, parameter.Value);
                }
                reader = db.ExecuteReader(cmd);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        long msgId = reader.GetInt64(0);
                        UserMessage msg = res.Find(delegate(UserMessage msg1) { return msg1.MessageID == msgId; });
                        if (msg == null)
                        {
                            msg = CreateUserMessage(reader, 0);
                            res.Add(msg);
                        }
                        // add comments to message
                        if (!reader.IsDBNull(8))
                        {
                            UserMessage comment = CreateUserMessage(reader, 8);
                            comment.Parent = msg;
                            if (msg.Commments == null)
                            {
                                msg.Commments = new List<UserMessage>();
                            }
                            msg.Commments.Add(comment);
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public UserMessage CreateUserMessage(IDataReader reader, int baseIndex)
        {
            UserMessage res = new UserMessage();
            res.MessageID = reader.GetInt64(baseIndex + 0);
            res.Sender = CreateUser(reader, baseIndex + 4);
            res.Subject = reader.IsDBNull(baseIndex + 1) ? "" : reader.GetString(baseIndex + 1);
            res.Message = reader.GetString(baseIndex + 2);
            res.MessageDate = reader.GetDateTime(baseIndex + 3);
            return res;
        }
        
        public User CreateUser(IDataReader reader, int baseIndex)
        {
            User res = new User();
            res.Personid = reader.GetInt64(baseIndex + 0);
            res.Firstname = reader.GetString(baseIndex + 1);
            res.Lastname = reader.GetString(baseIndex + 2);
            res.Emailaddress = reader.GetString(baseIndex + 3);
            return res;
        }

        public int GetMessagesCount()
        {
            string selectSql = "SELECT COUNT(id) FROM UserPost";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSql);
            int res = (int)db.ExecuteScalar(cmd);
            return res;
        }


        public int GetNewMessagesCount(long userID)
        {
            int res = 0;
            string selectSQL = @"SELECT COUNT(up.id) FROM userPost up INNER JOIN users u ON up.date > u.lastwallvisit
WHERE u.personid = @PERSONID AND up.parentid IS NULL AND up.personid <> @PERSONID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@PERSONID", DbType.Int64, userID);
            res = (int)db.ExecuteScalar(cmd);
            return res;
        }
        #endregion 

        #region Update

        public bool UpdateUserMessage(UserMessage msg)
        {
            bool res = false;
            string updateSql = "UPDATE dbo.UserPost SET Message = @MESSAGE, Date = @DATE WHERE ID = @ID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(updateSql);
            db.AddInParameter(cmd, "@ID", DbType.Int64, msg.MessageID);
            db.AddInParameter(cmd, "@MESSAGE", DbType.String, msg.Message);
            db.AddInParameter(cmd, "@DATE", DbType.DateTime, msg.MessageDate);
            res = db.ExecuteNonQuery(cmd) == 1;
            return res;
        }

        #endregion

        #region insert

        private DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSql = @"INSERT INTO dbo.UserPost (Personid,Subject,Message,Date,ParentID)
                                                     VALUES (@Personid,@Subject,@Message,@Date,@ParentID) 
                                    SET @ID = SCOPE_IDENTITY()";
            DbCommand cmd = db.GetSqlStringCommand(insertSql);
            db.AddInParameter(cmd, "@Personid", DbType.Int64);
            db.AddInParameter(cmd, "@Subject", DbType.String);
            db.AddInParameter(cmd, "@Message", DbType.String);
            db.AddInParameter(cmd, "@Date", DbType.Date);
            db.AddInParameter(cmd, "@ParentID", DbType.Int64);
            db.AddOutParameter(cmd, "@ID", DbType.Int64, 8);
            return cmd;
        }

        private void SetInsertParameters(SqlDatabase db, DbCommand cmd, UserMessage um)
        {
            db.SetParameterValue(cmd, "@Personid", um.Sender.Personid);
            db.SetParameterValue(cmd, "@Subject", String.IsNullOrEmpty(um.Subject) ? DBNull.Value : (object)um.Subject);
            db.SetParameterValue(cmd, "@Message", um.Message);
            db.SetParameterValue(cmd, "@Date", um.MessageDate);
            db.SetParameterValue(cmd, "@ParentID", um.Parent == null ? DBNull.Value : (object)um.Parent.MessageID);
        }

        public bool InsertMessage(UserMessage message)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertParameters(db, cmd, message);
            res = db.ExecuteNonQuery(cmd) == 1;
            message.MessageID = (long)db.GetParameterValue(cmd, "@ID");
            return res;
        }

        #endregion

        #region delete
        public void DeleteMessage(long messageID)
        {
            string deleteProcName = "[dbo].[DeleteUserPost]";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand(deleteProcName);
            db.AddInParameter(cmd, "@ID", DbType.Int64, messageID);
            db.ExecuteNonQuery(cmd);
        }

        #endregion
    }
}
